/*====================================================================================
                                                                     
		Financial Constraints and Propagation of Shocks in Production Networks
			    By Demir-Javorcik-Michalski-Ors
  
*====================================================================================*/	

clear all
set more off
log close _all
set matsize 5000

cd "\data\" // Add your directory here

/***************************************
/Load balance sheet data
****************************************/

use "Bilanco.dta",clear

rename sırano ID
rename sırano_donem year

keep if year==2010
keep ID v177 amaliborçlar bticariborçlar cdiðerborçlar v176 v184 v190 kýsavadeliborçlanmagiderleri uzunvadeliborçlanmagiderleri hfinansmangiderleri ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar  pasiftoplamý ıdönenvarlýklar ahazýrdeðerler bmenkulkýymetler abrütsatýþlar aktiftoplam  ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar yurtdýþýsatýþlar cticarialacaklar ddiðeralacaklar
for X in any v177 amaliborçlar bticariborçlar cdiðerborçlar v176 v184 v190 kýsavadeliborçlanmagiderleri uzunvadeliborçlanmagiderleri hfinansmangiderleri ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar  pasiftoplamý ıdönenvarlýklar ahazýrdeðerler bmenkulkýymetler abrütsatýþlar aktiftoplam  ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar yurtdýþýsatýþlar cticarialacaklar ddiðeralacaklar: destring X,replace

destring ID,replace
sort ID
save temp1.dta,replace

/***************************************
/Construct annual wages
****************************************/
use "Girisim Sicil.dta",clear

rename sırano ID
rename sırano_donem year

keep if year==2010
egen wage0=rowtotal(wages_q1 wages_q2 wages_q3 wages_q4) //quarterly wages
replace wage0=wage0*3 // annual wages
keep ID wage0 nace2
rename nace2 nace2010
sort ID
save temp2.dta,replace

/***************************************
/Construct material inputs
****************************************/

use "BABS.dta",clear 

rename sırano_alici buyer_ID
rename sırano_satici seller_ID
rename sırano_donem year

keep if year==2010

gen ID=buyer_ID
destring ID,replace
sort ID
merge ID using temp2.dta
keep if _merge==3
drop _merge

//Keep manufacturing and wholesale firms only
destring nace2010,replace
keep if (nace2010>9 & nace2010<34) | nace2010==45 | nace2010==46

collapse (sum) value,by( buyer_ID )

rename buyer_ID ID
rename value DomPurch0
sort ID

destring ID,replace
sort ID
merge ID using temp2.dta
keep if _merge==3
drop _merge
sort ID
save temp2.dta,replace


use "ithalat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet imports

sort ulke
merge ulke using "TUIK-country codes.dta" 
drop if _merge==2
drop if country_code==""
drop if country_code=="TUR"
drop _merge ulke

keep if year==2010

drop if regimecode==4051 | regimecode==4100 | regimecode==4171 | regimecode==4123 | regimecode==4191 | regimecode==4251 | regimecode==5100 | regimecode==5123 | regimecode==5141 | regimecode==5171 | regimecode==5191 | regimecode==5200 | regimecode==5221 | regimecode==5223 | regimecode==5271 | regimecode==5291 | regimecode==5341 | regimecode==5351 

gen HS6=floor(HS12/1000000)
collapse (sum) imports,by(ID country_code HS6)
bys ID ,sort: egen totalimports=sum(imports)
gen import_share=imports/totalimports

sort ID
merge ID using temp1.dta
drop _merge

sort ID
merge ID using temp2.dta
drop _merge

/***************************************
/Construct Bartik exposure
****************************************/
sort HS6 country_code
merge  HS6 country_code using "tempvarietyexp" // This dataset has to be transferred from TSI
drop _merge

for X in any  v177 amaliborçlar bticariborçlar cdiðerborçlar v176 v184 v190 kýsavadeliborçlanmagiderleri uzunvadeliborçlanmagiderleri hfinansmangiderleri ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar  pasiftoplamý ıdönenvarlýklar ahazýrdeðerler bmenkulkýymetler abrütsatýþlar aktiftoplam  ıvuzunvadeliyabancýkaynaklar ıııkýsavadeliyabancýkaynaklar yurtdýþýsatýþlar cticarialacaklar ddiðeralacaklar: replace X=0 if X==.

gen debt_ratio=(ıvuzunvadeliyabancýkaynaklar+ ıııkýsavadeliyabancýkaynaklar)/aktiftoplam
gen avgint_rate=hfinansmangiderleri/abrütsatýþlar

replace totalimports=0 if totalimports==.
drop if wage0==0
rename  abrütsatýþlar Sales0 
rename  yurtdýþýsatýþlar ForeignSales0
gen ImpInt0=1.5*totalimports/(wage0+DomPurch0+1.5*totalimports)
gen FirmExposure=(1.5*totalimports/(wage0+DomPurch0+1.5*totalimports))*import_share*VarietyExposure
gen TotalInp0=wage0+DomPurch0+1.5*totalimports

collapse (sum) FirmExposure (mean) avgint_rate  ImpInt0 debt_ratio Sales0  ForeignSales0  TotalInp0,by(ID)

sort ID
save temp3.dta,replace

/***************************************
/Exposure using processing imports
****************************************/
use "ithalat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet imports

sort ulke
merge ulke using "TUIK-country codes.dta" 
drop if _merge==2
drop if country_code==""
drop if country_code=="TUR"
drop _merge ulke

keep if year==2010

keep if regimecode==4051 | regimecode==4100 | regimecode==4171 | regimecode==4123 | regimecode==4191 | regimecode==4251 | regimecode==5100 | regimecode==5123 | regimecode==5141 | regimecode==5171 | regimecode==5191 | regimecode==5200 | regimecode==5221 | regimecode==5223 | regimecode==5271 | regimecode==5291 | regimecode==5341 | regimecode==5351 

gen HS6=floor(HS12/1000000)
collapse (sum) imports,by(ID country_code HS6)
bys ID ,sort: egen totalimports=sum(imports)
gen import_share=imports/totalimports


sort HS6 country_code
merge  HS6 country_code using "tempvarplacebo"  // This dataset has to be transferred from TSI
drop _merge

sort ID
merge ID using temp2.dta
drop _merge

sort ID
merge ID using temp3.dta
drop _merge

replace totalimports=0 if totalimports==.
drop if wage0==0

gen FirmExposureP=(1.5*totalimports/(wage0+DomPurch0+1.5*totalimports))*import_share*VarietyExposure

collapse (sum) FirmExposureP ,by(ID)

sort ID
merge ID using temp3.dta
drop _merge
sort ID
save temp3.dta,replace

/***************************************
/Construct panel variables
****************************************/

use "Girisim Sicil.dta",clear

rename sırano ID
rename sırano_donem year

keep if year>2007 & year<2015
egen wage=rowtotal(wages_q1 wages_q2 wages_q3 wages_q4)
replace wage=wage*3
keep ID year wage nace2
sort ID year
save temp4.dta,replace

use "BABS.dta",clear 

rename sırano_alici buyer_ID
rename sırano_satici seller_ID
rename sırano_donem year

keep if year>2007 & year<2015
collapse (sum) value,by(seller_ID buyer_ID year)

gen ID=buyer_ID
destring ID,replace
sort ID year
merge ID year using temp4.dta
drop _merge

//Keep manufacturing and wholesalefirms only
keep if (nace>9 & nace<34) | nace==45 | nace==46

collapse (sum) value (mean) wage,by( ID year)
rename value DomPurch

destring ID,replace
sort ID year
save temp4.dta,replace


//Construct firm-level imports
use "ithalat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet imports

sort ulke
merge ulke using "TUIK-country codes.dta" 
drop if _merge==2
drop if country_code==""
drop if country_code=="TUR"
drop _merge ulke

keep if year>2007 & year<2015

gen importsP=imports if regimecode==4051 | regimecode==4100 | regimecode==4171 | regimecode==4123 | regimecode==4191 | regimecode==4251 | regimecode==5100 | regimecode==5123 | regimecode==5141 | regimecode==5171 | regimecode==5191 | regimecode==5200 | regimecode==5221 | regimecode==5223 | regimecode==5271 | regimecode==5291 | regimecode==5341 | regimecode==5351 

collapse (sum) imports importsP,by(ID year)

sort ID year
merge ID year using temp4.dta
drop _merge 

drop if wage==0 | wage==.
replace imports=0 if imports==.
replace importsP=0 if importsP==.

gen ImpInt=.
for X in any 1.29 1.55 1.50 1.67 1.79 1.90 2.19 \ Y in any 2008 2009 2010 2011 2012 2013 2014: replace ImpInt=X*imports/(wage+DomPurch+X*imports) if year==Y
sort ID year
save temp4.dta,replace


//Construct initial  firm size
use "Girisim Sicil.dta",clear

rename sırano ID
rename sırano_donem year

keep if year==2010

for X in any q1 q2 q3 q4: replace employment_X=. if employment_X==0
egen empl0=rowmean(employment_q1-employment_q4)

keep ID nace4 empl0 province_code

sort ID
merge ID using temp3.dta
drop _merge

sort ID
save temp3.dta,replace

// Firm (headquarter) industry of operation
use "Girisim Sicil.dta",clear

rename sırano ID
rename sırano_donem year

keep if year>2007 & year<2015
//Keep manufacturing and wholesale firms only
destring nace2,replace
keep if (nace>9 & nace<34) | nace==45 | nace==46

for X in any q1 q2 q3 q4: replace employment_X=. if employment_X==0
egen empl=rowmean(employment_q1-employment_q4)

destring nace4,replace

keep ID year nace4 nace2 estabyear province_code empl

//Merge with other time-varying data
sort ID year
merge ID year using temp4.dta
drop _merge

destring nace2,replace
keep if (nace>9 & nace<34) | nace==45 | nace==46

sort ID year
save temp4.dta,replace


//Load balance sheet data
use "Bilanco.dta",clear

rename sırano ID
rename sırano_donem year

keep if year>2007 & year<2015

keep ID year yurtdýþýsatýþlar  abrütsatýþlar 

for X in any yurtdýþýsatýþlar  abrütsatýþlar : destring X,replace
for X in any yurtdýþýsatýþlar  abrütsatýþlar : replace X=. if X==0

collapse (mean) yurtdýþýsatýþlar  abrütsatýþlar  ,by(ID year )  

destring ID,replace
sort ID year
merge ID year using temp4.dta
drop _merge

rename abrütsatýþlar Sales
rename yurtdýþýsatýþlar ForeignSales
gen ShrExports=ForeignSales/Sales
gen ShrMatInp=.
for X in any 1.29 1.55 1.50 1.67 1.79 1.90 2.19 \ Y in any 2008  2009 2010 2011 2012 2013 2014:replace ShrMatInp=(DomPurch+X*imports)/(wage+DomPurch+X*imports) if year==Y
gen AvgCost=.
for X in any 1.29 1.55 1.50 1.67 1.79 1.90 2.19 \ Y in any 2008 2009 2010 2011 2012 2013 2014:replace AvgCost=(wage+DomPurch+X*imports)/Sales if year==Y

keep if Sales+wage!=.

sort ID year
save temp4.dta,replace

/***************************************
/Grouping firms
****************************************/

use temp3.dta,clear

drop if Sales0==0
drop if Sales0==.

drop if empl0==0
drop if empl0==.

bys nace4,sort: egen msize=mean(empl0)
gen Large=empl0>msize

bys nace4,sort: egen mavgint=mean(avgint_rate)
gen LowFinCost=avgint_rate<mavgint

sort ID
save temp3.dta,replace

/***************************************
/Construct centrality measures using VAT data
****************************************/

use "BABS.dta",clear 

rename sırano_alici buyer_ID
rename sırano_satici seller_ID
rename sırano_donem year

keep if year>2007 & year<2015
collapse (sum) value,by(seller_ID buyer_ID year)


//Merge with location & employment data
gen  ID=buyer_ID
destring ID,replace
sort ID 
merge ID  using "temp3.dta"
drop if nace4==.
keep year seller_ID buyer_ID value nace4 Sales0 TotalInp0    FirmExposureP FirmExposure ImpInt0 Large  LowFinCost avgint_rate empl0 debt_ratio msize  mdebt mavgint ForeignSales0 

for X in any nace4 Sales0 TotalInp0    FirmExposureP FirmExposure ImpInt0 Large  LowFinCost avgint_rate empl0 debt_ratio msize  mdebt mavgint ForeignSales0 : rename X X_b

gen  ID=seller_ID
destring ID,replace
sort ID 
merge ID  using "temp3.dta"
drop if nace4==.
keep year seller_ID buyer_ID value nace4*  Sales0* TotalInp0*  FirmExposureP* FirmExposure* ImpInt0* Large* LowFinCost*  avgint_rate*  empl0* debt_ratio* msize*  mdebt*  mavgint* ForeignSales0*

for X in any nace4 Sales0 TotalInp0    FirmExposureP FirmExposure ImpInt0 Large  LowFinCost avgint_rate empl0 debt_ratio msize  mdebt mavgint ForeignSales0: rename X X_s


drop if value==0
drop if value==.
save temp5.dta,replace


//Merge with Exposure data
use temp5,clear

keep if year==2010

gen Exposure_b=FirmExposure_b*value/Sales0_s
gen Exposure_s=FirmExposure_s*value/TotalInp0_b

gen Exposure_b_HFC=FirmExposure_b*value/Sales0_s if LowFinCost_b==0
gen Exposure_b_LFC=FirmExposure_b*value/Sales0_s if LowFinCost_b==1

gen Exposure_b_Emp=(empl0_b/msize_b)*FirmExposure_b*value/Sales0_s 
gen Exposure_s_Emp=(empl0_s/msize_s)*FirmExposure_s*value/TotalInp0_b 

save temp7.dta,replace

use temp7,clear
collapse (sum) Exposure_s* ,by(buyer_ID )
rename buyer_ID ID
destring ID,replace
sort ID
save temp8.dta,replace

use temp7,clear
collapse (sum) Exposure_b* ,by(seller_ID )
rename seller_ID ID
destring ID,replace
sort ID
save temp9.dta,replace

/***************************************
/Centrality measures
****************************************/
use temp5.dta,clear

sort year buyer_ID seller_ID
by year buyer_ID seller_ID: gen indegree_total=_n==1
by year buyer_ID: replace indegree_total=sum(indegree_total)
by year buyer_ID: replace indegree_total=indegree_total[_N]

sort year seller_ID buyer_ID
by year seller_ID buyer_ID: gen outdegree_total=_n==1
by year seller_ID: replace outdegree_total=sum(outdegree_total)
by year seller_ID: replace outdegree_total=outdegree_total[_N]

save temp13.dta,replace

use temp13.dta,replace

rename value Purch_Total

collapse (sum) Purch_Total (mean) indegree_total  ,by(buyer_ID  year)

rename buyer_ID ID
destring ID,replace
sort ID year
save temp14.dta,replace
